﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Text.RegularExpressions;
using ConverNumToString;

namespace _201206QuanLyBanHang.ChucNang
{
    public partial class DMKhachhang : Form
    {
        
        //khai báo biến
        private int makh;
        private int maloaikh;
        private int makhuvuc;
        private string tenkh;
        private string tencuahang;
        private string diachikh;
        private string dienthoaikh;
        private int tinhtrangkh;
        private string ghichu_kh;
        private int stt;
        private string sanpham;

        Hashtable htb;
        
        bool flat = false;
        string str_def = "default ";
        DateTime date_def = DateTime.Now;



        ConvertNumToStr objConvert = new ConvertNumToStr(); 
        private int sodong = 0;
        //them danh sach lay theo dieu kien ten loai va khu vuc
        private string sqlStr1 = "SELECT [MAKH],[MALOAIKH],[MAKHUVUC],[TENKH],[TENCUAHANG],[DIACHIKH],[DIENTHOAIKH],[TINHTRANGKH],[SANPHAM],[GHICHU_KH],[STT] FROM [KHACHHANG]WHERE TINHTRANGKH=0";
        private string sqlStr = "SELECT [MAKH],[MALOAIKH],[MAKHUVUC],[TENKH],[TENCUAHANG],[DIACHIKH],[DIENTHOAIKH],[TINHTRANGKH],[SANPHAM],[GHICHU_KH], [STT] FROM [KHACHHANG]WHERE TINHTRANGKH=1 order by [MAKH] DESC";

        private BLL_DMKhachHang db = new BLL_DMKhachHang();
        private DataSet ds = new DataSet();
        public DMKhachhang()
        {
            InitializeComponent();
            DataBind(sqlStr);
            db.fillCombo("select makhuvuc,tenkhuvuc from khuvuc", this.CbKhuvuc, "tenkhuvuc", "makhuvuc");
            db.fillCombo("select maloaikh,tenloaikh from loaikh", this.cbLoaiKH, "tenloaikh", "maloaikh");
           // db.fillcheckedListBox("select masp,tensp from sanpham", this.clbsanpham1, "tensp", "masp");
            clear();
            CbKhuvuc.SelectedIndex = -1;
            CbKhuvuc.Text = "---Chọn khu vực---";
           this.cbLoaiKH .SelectedIndex = -1;
            this.cbLoaiKH.Text = "---Chọn loại---";
            //hàm view du lieu len tren checklistbox.

           viewdautien();
           anhiencontrol(false);
            //for (int i = 1; i <= 615; i++)
            //{
            //    insertcsdl(i);
            //}
        }
        public void DataBind1(string sql)
        {
            ds = db.getDMKhachHang(sql);
            this.dgDmKhachHang.DataSource = ds.Tables[0];
            this.dgDmKhachHang.Columns[0].HeaderText = "MaKH";
            this.dgDmKhachHang.Columns[1].HeaderText = "MaLoaiKH";
            this.dgDmKhachHang.Columns[1].Visible = false;
            this.dgDmKhachHang.Columns[2].HeaderText = "Mã khu vực";
            this.dgDmKhachHang.Columns[2].Visible = false;
            this.dgDmKhachHang.Columns[3].HeaderText = "Tên khách hàng";
            this.dgDmKhachHang.Columns[3].Visible = false;
            this.dgDmKhachHang.Columns[4].HeaderText = "Tên cửa hàng";
            this.dgDmKhachHang.Columns[5].HeaderText = "Địa chỉ";
            this.dgDmKhachHang.Columns[6].HeaderText = "Điện thoại";
            this.dgDmKhachHang.Columns[7].HeaderText = "Tình trạng";
            this.dgDmKhachHang.Columns[7].Visible = false;
            this.dgDmKhachHang.Columns[8].HeaderText = "Sản phẩm";
            this.dgDmKhachHang.Columns[8].Visible = true;
            this.dgDmKhachHang.Columns[9].HeaderText = "Ghi chú";
            this.dgDmKhachHang.Columns[10].HeaderText = "STT";
            this.dgDmKhachHang.AutoResizeColumns(
              DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

            this.dgDmKhachHang.ReadOnly = false;
            dgDmKhachHang.AllowUserToAddRows = false;
            sodong = 0;
            sodong = ds.Tables[0].Rows.Count + 1;
            this.Gropsodong.Text = "Danh sách khách hàng: số lượng khách hàng:";
            this.Gropsodong.Text = this.Gropsodong.Text + sodong;

        }
        public void DataBind(string sql)
        {
            ds = db.getDMKhachHang(sql);
            this.dgDmKhachHang.DataSource = ds.Tables[0];
            this.dgDmKhachHang.Columns[0].HeaderText = "MaKH";
            this.dgDmKhachHang.Columns[1].HeaderText = "MaLoaiKH";
            this.dgDmKhachHang.Columns[1].Visible = false;
            this.dgDmKhachHang.Columns[2].HeaderText = "Mã khu vực";
            this.dgDmKhachHang.Columns[2].Visible = false;
            this.dgDmKhachHang.Columns[3].HeaderText = "Tên khách hàng";
            this.dgDmKhachHang.Columns[3].Visible = false;
            this.dgDmKhachHang.Columns[4].HeaderText = "Tên cửa hàng";
            this.dgDmKhachHang.Columns[5].HeaderText = "Địa chỉ";
            this.dgDmKhachHang.Columns[6].HeaderText = "Điện thoại";
            this.dgDmKhachHang.Columns[7].HeaderText = "Tình trạng";
            this.dgDmKhachHang.Columns[7].Visible = false;
            this.dgDmKhachHang.Columns[8].HeaderText = "Sản phẩm";
            this.dgDmKhachHang.Columns[8].Visible = true;
            this.dgDmKhachHang.Columns[9].HeaderText = "Ghi chú";
            this.dgDmKhachHang.Columns[10].HeaderText = "STT";
           this.dgDmKhachHang.AutoResizeColumns(
             DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

            this.dgDmKhachHang.ReadOnly = false;
            dgDmKhachHang.AllowUserToAddRows = false;
            sodong = 0;
            sodong = ds.Tables[0].Rows.Count;
            this.Gropsodong.Text = "Danh sách khách hàng: số lượng khách hàng:";
            this.Gropsodong.Text = this.Gropsodong.Text + sodong;
            
        }
        // thêm cơ sở dữ liệu
        private void insertcsdl (int in_mkh)
        {
            //thêm vào -- mục đích xét quà biếu
            // Thêm vào Kh_duoc_dt là giá trị default 0 
            string err = "";
            if (!db.insert_Kh_duoc_dt_default(ref err, 1, makh, date_def, 0, str_def, 0, 0, date_def, 0))
            {
                MessageBox.Show(err.ToString());
            }
            //if (!db.insert_Kh_duoc_dt_default(ref err,1,makh,date_def,0,str_def,0,0,date_def,0))
            //{
            //    MessageBox.Show(err.ToString());
            //}
            //thêm vào kh_mua_sp
            try
            {
                db.insert_kh_mua_sp_default(in_mkh, date_def, 1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //thêm vào khachhnag_thanhtoan
            try
            {
                db.insert_kh_mua_sp_default(ref err, 1, makh, date_def, 1, 0, 0, 0, 0, str_def);
               // db.insert_khachhang_thanhtoan_default(in_mkh, date_def, 1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void anhiencontrol(bool t)
        {
            txtdiachi.Enabled = t;
            txtdienthoai.Enabled = t;
            txtghichu.Enabled = t;
            txtmakh.Enabled = t;
            txtstt.Enabled = t;
            cbLoaiKH.Enabled = t;
            clbsanpham1.Enabled = t;
            txttench.Enabled = t;
            txtdienthoai.Enabled = t;
            cbtinhtrang.Enabled = t;
            //CbKhuvuc.Enabled = t;
            BtnSua.Enabled = t;
            BtnXoa.Enabled = t;
            BtnThem.Enabled = !t;
            cktxconmua.Enabled = t;
        }
        /// <summary>
        /// hàm sử dụng để lưu khách hàng
        /// </summary>
        private void luu()
        {
            makh = int.Parse(this.txtmakh.Text);
            maloaikh = int.Parse(this.cbLoaiKH.SelectedValue.ToString());
            makhuvuc=int.Parse(this.CbKhuvuc.SelectedValue.ToString());
            tenkh = this.txttenkh.Text;
            tencuahang = this.txttench.Text;
            diachikh = this.txtdiachi.Text;
            dienthoaikh = this.txtdienthoai.Text;
            
            if (cbtinhtrang.Checked)
                tinhtrangkh = 1;
            else
                tinhtrangkh = 0;
            sanpham = laysanphamtronglist();
            ghichu_kh = this.txtghichu.Text;
            stt = Convert.ToInt32(txtstt.Text);
            string err = "";
            if (!db.insertDMKhachHang(ref err,makh, maloaikh,makhuvuc,tenkh,tencuahang,diachikh,dienthoaikh,tinhtrangkh,sanpham,ghichu_kh,stt)) 
            {
                MessageBox.Show(err.ToString());
            }

            //thêm vào -- mục đích xét quà biếu
            // Thêm vào Kh_duoc_dt là giá trị default 0 
            if (!db.insert_Kh_duoc_dt_default(ref err, 1, makh, date_def, 0,str_def, 0, 0, date_def,0))
            {
                MessageBox.Show(err.ToString());
            }

            //thêm ngày 22/11/2012
            //thêm vào kh_mua_sp
            try
            {
                db.insert_kh_mua_sp_default(ref err, 1, makh, date_def, 1, 0, 0, 0, 0, str_def);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //thêm vào khachhnag_thanhtoan
            try
            {
                db.insert_khachhang_thanhtoan_default(ref err, makh, date_def, 0, 1, str_def);
                //db.insert_khachhang_thanhtoan_default(makh, date_def, 1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //anh phuc DataBind(sqlStr);
            // Chỉ hiển thị danh sách các khách hàng được thêm tại khu vực đó.
            string sql = "SELECT [MAKH],[MALOAIKH],[MAKHUVUC],[TENKH],[TENCUAHANG],[DIACHIKH],[DIENTHOAIKH],[TINHTRANGKH],[sanpham],[GHICHU_KH], [stt] FROM [KHACHHANG]WHERE TINHTRANGKH=1 and makhuvuc=" + "'" + CbKhuvuc.SelectedValue.ToString() + "'";
            DataBind(sql);
        }
        /// <summary>
        /// hàm sửa thông tin
        /// </summary>
        private void DMSua()
        {
            string err = "";
            makh = int.Parse(this.txtmakh.Text);
            maloaikh =int.Parse(this.cbLoaiKH.SelectedValue.ToString());
            makhuvuc = int.Parse(this.CbKhuvuc.SelectedValue.ToString());
            
            tenkh = this.txttenkh.Text;
            tencuahang = this.txttench.Text;
            diachikh = this.txtdiachi.Text;
            dienthoaikh = this.txtdienthoai.Text;
            if (cbtinhtrang.Checked)
                tinhtrangkh = 1;
            else
                tinhtrangkh = 0;
            sanpham = laysanphamtronglist();
            ghichu_kh = this.txtghichu.Text;
            stt = Convert.ToInt32(txtstt.Text);
            if (!db.updateDMKhachHang(ref err,makh,maloaikh,makhuvuc,tenkh,tencuahang,diachikh,dienthoaikh,tinhtrangkh,sanpham,ghichu_kh,stt))
            {
                MessageBox.Show(err.ToString());
            }
            DataBind(sqlStr);
        }
        private void bin()
        {
            this.txtmakh.Text = dgDmKhachHang[dgDmKhachHang.Columns[0].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.CbKhuvuc.SelectedValue =dgDmKhachHang[dgDmKhachHang.Columns[2].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.cbLoaiKH.SelectedValue=dgDmKhachHang[dgDmKhachHang.Columns[1].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.txttenkh.Text = dgDmKhachHang[dgDmKhachHang.Columns[3].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.txttench.Text = dgDmKhachHang[dgDmKhachHang.Columns[4].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.txtdiachi.Text = dgDmKhachHang[dgDmKhachHang.Columns[5].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            this.txtdienthoai.Text = dgDmKhachHang[dgDmKhachHang.Columns[6].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            if (int.Parse(dgDmKhachHang[dgDmKhachHang.Columns[7].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString()) == 1)
                cbtinhtrang.Checked = true;
            else
                cbtinhtrang.Checked = false;
            //===================================
            //dua du lieu vao checklistbox
            string[] dulieu = dgDmKhachHang[dgDmKhachHang.Columns[8].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString().Split('+');

            for (int i = 0; i < clbsanpham1.Items.Count; i++)
            {
                clbsanpham1.SetItemChecked(i, false);
            }
            foreach (string item in dulieu)
            {
                clbsanpham1.SetItemChecked(Convert.ToInt32(item) - 1, true);
            }
            
           
            
         
            //===================================
            this.txtghichu.Text = dgDmKhachHang[dgDmKhachHang.Columns[9].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            txtstt.Text = dgDmKhachHang[dgDmKhachHang.Columns[10].Index, dgDmKhachHang.CurrentRow.Index].Value.ToString();
            //bien co de load du lieu
           
            flat = true;
        }
        public int laystt()
        {
            int stt = 1;

            string sqlstring = "select max(Makh) from khachhang";
            string err = "";
            object kq = db.layma(ref err, sqlstring);
            if (kq != System.DBNull.Value)
                stt = Convert.ToInt32(kq) + 1;
            return stt;
        }
        //ham chuyen chu hoa

        public string ToTitleCase(string str)
        {
            str = str.ToLower();
            char[] charArr = str.ToCharArray();
            charArr[0] = char.ToUpper(charArr[0]);
            foreach (Match m in Regex.Matches(str,@"\s\S"))
            {
                charArr[m.Index + 1] = m.Value.ToUpper().Trim()[0];   
            }
            return new string(charArr);
        }
        private void BtnSua_Click(object sender, EventArgs e)
        {
            if (this.CbKhuvuc.SelectedIndex == -1)
            {
                MessageBox.Show("Chọn khu vực");
                CbKhuvuc.Focus();
            }
            else if (this.cbLoaiKH.SelectedIndex == -1)
            {
                MessageBox.Show("Chọn loại khách hàng");
                cbLoaiKH.Focus();
            }
            else if (this.txttench.Text == "")
            {
                MessageBox.Show("nhập tên khách hàng");
                this.txttench.Focus();
            }
            else
            {
                this.BtnSua.Enabled = false;
                this.BtnXoa.Enabled = false;
                if (flat)
                {
                    DMSua();
                    clear();
                    anhiencontrol(false);
                    MessageBox.Show("chỉnh sửa thành công");
                }
                else
                {
                    luu();
                    clear();
                    anhiencontrol(false);
                    MessageBox.Show("Lưu thành công");
                }
            }
        }
        
        private void BtnThem_Click(object sender, EventArgs e)
        {
            flat = false;
            //setdoituong();
            anhiencontrol(true);
            this.BtnSua.Enabled = true;
            this.BtnXoa.Enabled = true;
            this.CbKhuvuc.Focus();
           //reset lai control
            clear();
           this.txtmakh.Text = laystt().ToString();
           txtstt.Text = txtmakh.Text;
        }
        /// <summary>
        /// hàm reset lai cac control
        /// </summary>
        private void clear()
        {
            this.txtmakh.Text = "";
            this.txttenkh.Text = "";
            this.txttench.Text= "";
            this.txtghichu.Text = "";
            txtdienthoai.Text = "";
            this.txtdiachi.Text="";
            cbtinhtrang.Checked = true;
            cbLoaiKH.SelectedIndex = -1;
            CbKhuvuc.SelectedIndex = -1;
            txtstt.Text = "";
            clbsanpham1.ClearSelected();

        }
        private void txtdiachi_Leave(object sender, EventArgs e)
        {
            if (txtdiachi.Text != "")
            {
                string t = txtdiachi.Text;
                txtdiachi.Text = ToTitleCase(t);
            }
        }

        private void txttenkh_Leave(object sender, EventArgs e)
        {
            if (txttenkh.Text != "")
            {
                string t = txttenkh.Text;
                txttenkh.Text = ToTitleCase(t);
            }
        }

        private void dgDmKhachHang_Click(object sender, EventArgs e)
        {
            try
            {

                this.dgDmKhachHang.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
               // this.dgDmKhachHang.BeginEdit(false);
                this.dgDmKhachHang.CurrentRow.Selected = true;
                this.bin();
                this.BtnSua.Enabled = true;
                this.BtnXoa.Enabled = true;
                anhiencontrol(true);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void DMXoa()
        {
            //int id;
            try
            {
               makh = int.Parse(this.txtmakh.Text);
            }
            catch
            {
                MessageBox.Show("Chọn khách hàng để xóa");
                return;
            }

            DialogResult result;
            result = MessageBox.Show("Bạn có muốn xóa không", "Xóa dữ liệu trong danh mục khách hàng", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {
                string err = "Dữ liệu không thể xóa";
                if (!db.deleteDMKhachHang(ref err,makh))
                    MessageBox.Show(err);
                else
                    MessageBox.Show("Đã xóa thành công");
            }
            DataBind(sqlStr);

        }
        private void BtnXoa_Click(object sender, EventArgs e)
        {
            if (khachhangconno())
            {
                double t = laytienthanhtoan(txtmakh.Text);
                double n = laytienno(txtmakh.Text);
                double sono = n - t;
                MessageBox.Show("khách hàng còn nợ: " + sono.ToString() + " đồng \n" + objConvert.converNumToString(objConvert.slipArray(Convert.ToString(sono.ToString()))) +" đồng");
                anhiencontrol(false);
            }
            else
            {
                capnhattinhtrang();
                MessageBox.Show("Khách hàng " + txttench.Text + " còn nợ.\n" + "Khách hàng sẽ được cập nhật tình trạng là không giao dịch");
                clear();
                this.BtnSua.Enabled = false;
                this.BtnXoa.Enabled = false;
                anhiencontrol(false);
            }
        }
        public double laytienno(string makh)
        {
            double stt = 0;

            string sqlstring = "select sum(gia_sp_ngaymua+gia_chietkhau_ngaymua)*soluongmua as tienno from kh_mua_sp where makh="+"'"+makh+"'"+ "group by makh,soluongmua";
            string err = "";
            object kq = db.layma(ref err, sqlstring);
            if (kq != System.DBNull.Value)
                stt = Convert.ToDouble(kq);
            return stt;
        }
        public double laytienthanhtoan(string makh)
        {
            double stt = 0;

            string sqlstring = "select sum(sotienthanhtoan)as tienthanhtoan from khachhang_thanhtoan where makh="+"'"+makh+"'"+ " group by makh";
            string err = "";
            object kq = db.layma(ref err, sqlstring);
            if (kq != System.DBNull.Value)
                stt = Convert.ToDouble(kq);
            return stt;
        }
        //hàm tính tiền nợ của khách hàng
        private bool khachhangconno()
        {
            bool kq = true;
            //MessageBox.Show("tien no: " + laytienno(txtmakh.Text));
            //MessageBox.Show("tien thanh toan: " + laytienthanhtoan(txtmakh.Text));
            if (laytienno(txtmakh.Text) > laytienthanhtoan(txtmakh.Text))
            {
                kq = true;
            }
            else
            {
                kq = false;
            }
            return kq;
        }
        private void capnhattinhtrang()
        {
            string err = "";
            makh = int.Parse(this.txtmakh.Text);
            tinhtrangkh = 0;
            if (!db.updateTTKhachHang(ref err, makh,tinhtrangkh))
            {
                MessageBox.Show(err.ToString());
            }
            DataBind(sqlStr);
        }

        private void checkBoxX1_CheckedChanged(object sender, EventArgs e)
        {
            if (this.cktxconmua.Checked)
                DataBind(sqlStr);
            else
                DataBind1(sqlStr1);
        }
        
        private void viewdautien()
        {
            DataTable dtsanpham = new DataTable();
            DataSet dssanpham = new DataSet();
            dssanpham = db.laysanpham("select masp,tensp from sanpham");
            dtsanpham = dssanpham.Tables[0];
            htb = new Hashtable();
           
            for (int i = 0; i < dtsanpham.Rows.Count; i++)
            {
                if (htb.ContainsKey(dtsanpham.Rows[i]["tensp"].ToString()) == false)
                {
                    string t = dtsanpham.Rows[i]["tensp"].ToString();
                    htb.Add(dtsanpham.Rows[i]["tensp"].ToString(), dtsanpham.Rows[i]["masp"].ToString());
                    clbsanpham1.Items.Add(t,CheckState.Unchecked);
                }
            }
            
        }
        private void DMKhachhang_Load(object sender, EventArgs e)
        {
            
           
        }

        
        private void CbKhuvuc_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (CbKhuvuc.SelectedIndex == -1) return;
            else if (CbKhuvuc.SelectedIndex == 0)
            {
                string sql = "SELECT [MAKH],[MALOAIKH],[MAKHUVUC],[TENKH],[TENCUAHANG],[DIACHIKH],[DIENTHOAIKH],[TINHTRANGKH],[SANPHAM],[GHICHU_KH], [STT] FROM [KHACHHANG]WHERE TINHTRANGKH=1 and makhuvuc=1";
                DataBind(sql);
            }
            else
            {

                string sql = "SELECT [MAKH],[MALOAIKH],[MAKHUVUC],[TENKH],[TENCUAHANG],[DIACHIKH],[DIENTHOAIKH],[TINHTRANGKH],[SANPHAM],[GHICHU_KH] , [STT] FROM [KHACHHANG]WHERE TINHTRANGKH=1 and makhuvuc=" + "'" + CbKhuvuc.SelectedValue.ToString() + "'";
                DataBind(sql);
            }
            
        }

       
        private string laysanphamtronglist()
        {
            string kq = null;
            if (clbsanpham1.CheckedIndices.Count > 0)
            {
                for (int i = 0; i < clbsanpham1.CheckedIndices.Count; i++)
                {
                    kq += htb[clbsanpham1.Items[i].ToString()].ToString() + "+";
                }
                return kq.Remove(kq.LastIndexOf('+'));
            }
            else
                return kq = "";
        }
        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show(laysanphamtronglist());
        }

        private void dgDmKhachHang_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

       

        

       
       

       
       
        
    }
}
